﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Data;
using System.Collections;
using System.Windows.Forms;

namespace PMSSys
{
    public class patientController
    {
        private DBconnentor dbConnect;

        public DBconnentor DbConnect
        {
            get { return dbConnect; }
            set { dbConnect = value; }
        }
        public String add(Patient patient)
        {
            MySqlCommand query = new MySqlCommand(@"INSERT INTO patient_profile( p_fname,  p_lname,  p_mname, p_bday, p_sex,  
            p_address,  p_telnum,  p_cnumber,  p_fathername,  p_fatheroccu, p_mothername,  p_motheroccu,  p_siblingcnt,  p_annualincome,  p_referral) 
            VALUES(@FNAME, @LNAME, @MNAME, @BDAY, @SEX, @ADDRESS, @TELNUM, @CNUMBER, @FATHERNAME, @FATHEROCCU,
            @MOTHERNAME, @MOTHEROCCU, @SIBLINGCNT, @ANNUALINCOME, @REFERRAL)", dbConnect.getConnection());

            query.Parameters.Add(new MySqlParameter("@FNAME", MySqlDbType.String)).Value = patient.FName;
            query.Parameters.Add(new MySqlParameter("@LNAME", MySqlDbType.String)).Value = patient.LName;
            query.Parameters.Add(new MySqlParameter("@MNAME", MySqlDbType.String)).Value = patient.MName;
            query.Parameters.Add(new MySqlParameter("@BDAY", MySqlDbType.DateTime)).Value = patient.Birthday;
            query.Parameters.Add(new MySqlParameter("@SEX", MySqlDbType.VarChar)).Value = patient.Sex;
            query.Parameters.Add(new MySqlParameter("@ADDRESS", MySqlDbType.String)).Value = patient.Address;
            query.Parameters.Add(new MySqlParameter("@TELNUM", MySqlDbType.String)).Value = patient.TelNo;
            query.Parameters.Add(new MySqlParameter("@CNUMBER", MySqlDbType.String)).Value = patient.CelNo;
            query.Parameters.Add(new MySqlParameter("@FATHERNAME", MySqlDbType.String)).Value = patient.FatherName;
            query.Parameters.Add(new MySqlParameter("@FATHEROCCU", MySqlDbType.String)).Value = patient.FatherOccu;
            query.Parameters.Add(new MySqlParameter("@MOTHERNAME", MySqlDbType.String)).Value = patient.MotherName;
            query.Parameters.Add(new MySqlParameter("@MOTHEROCCU", MySqlDbType.String)).Value = patient.MotherOccu;
            query.Parameters.Add(new MySqlParameter("@SIBLINGCNT", MySqlDbType.Int32)).Value = patient.SiblingCount;
            query.Parameters.Add(new MySqlParameter("@ANNUALINCOME", MySqlDbType.Int32)).Value = patient.AnnualInc;
            query.Parameters.Add(new MySqlParameter("@REFERRAL", MySqlDbType.String)).Value = patient.Refferal;

            if (dbConnect.nonQueryExecute(query))
            {
                return dbConnect.lastID();
            }
            else
                return String.Empty;
        }
        public DataTable getDataTable()
        {
            string str = "SELECT p_id, p_fname AS `First Name`, p_lname AS `Last Name`, p_telnum AS 'Tel No', p_cnumber AS `Cel No`, p_fathername AS `Father's Name`, p_mothername AS `Mother's Name` FROM patient_profile";
            return dbConnect.view(str);
        }
        public bool update(Patient patient)
        {
            MySqlCommand query = new MySqlCommand(@"UPDATE patient_profile SET p_fname = @FNAME,  p_lname = @LNAME,  p_mname = @MNAME, p_bday = @BDAY , p_sex = @SEX,  
            p_address = @ADDRESS,  p_telnum = @TELNUM,  p_cnumber = @CNUMBER,  p_fathername = @FATHERNAME,  p_fatheroccu = @FATHEROCCU, 
            p_mothername = @MOTHERNAME,  p_motheroccu = @MOTHEROCCU, p_siblingcnt = @SIBLINGCNT,  p_annualincome = @ANNUALINCOME,  p_referral = @REFERRAL WHERE p_id = @PATIENTID", dbConnect.getConnection());

            query.Parameters.Add(new MySqlParameter("@PATIENTID", MySqlDbType.String)).Value = patient.PatientID;
            query.Parameters.Add(new MySqlParameter("@FNAME", MySqlDbType.String)).Value = patient.FName;
            query.Parameters.Add(new MySqlParameter("@LNAME", MySqlDbType.String)).Value = patient.LName;
            query.Parameters.Add(new MySqlParameter("@MNAME", MySqlDbType.String)).Value = patient.MName;
            query.Parameters.Add(new MySqlParameter("@BDAY", MySqlDbType.DateTime)).Value = patient.Birthday;
            query.Parameters.Add(new MySqlParameter("@SEX", MySqlDbType.VarChar)).Value = patient.Sex;
            query.Parameters.Add(new MySqlParameter("@ADDRESS", MySqlDbType.String)).Value = patient.Address;
            query.Parameters.Add(new MySqlParameter("@TELNUM", MySqlDbType.String)).Value = patient.TelNo;
            query.Parameters.Add(new MySqlParameter("@CNUMBER", MySqlDbType.String)).Value = patient.CelNo;
            query.Parameters.Add(new MySqlParameter("@FATHERNAME", MySqlDbType.String)).Value = patient.FatherName;
            query.Parameters.Add(new MySqlParameter("@FATHEROCCU", MySqlDbType.String)).Value = patient.FatherOccu;
            query.Parameters.Add(new MySqlParameter("@MOTHERNAME", MySqlDbType.String)).Value = patient.MotherName;
            query.Parameters.Add(new MySqlParameter("@MOTHEROCCU", MySqlDbType.String)).Value = patient.MotherOccu;
            query.Parameters.Add(new MySqlParameter("@SIBLINGCNT", MySqlDbType.Int32)).Value = patient.SiblingCount;
            query.Parameters.Add(new MySqlParameter("@ANNUALINCOME", MySqlDbType.Int32)).Value = patient.AnnualInc;
            query.Parameters.Add(new MySqlParameter("@REFERRAL", MySqlDbType.String)).Value = patient.Refferal;

            return dbConnect.nonQueryExecute(query);
        }
        public DataTable searchTable(String query)
        {
            string str = "SELECT p_id, p_fname AS `First Name`, p_lname AS `Last Name`, p_telnum AS 'Tel No', p_cnumber AS `Cel No`, p_fathername AS `Father's Name`, p_mothername AS `Mother's Name` FROM patient_profile ";
            str += "WHERE p_fname LIKE '%" + query + "%' OR p_lname LIKE '%" + query
                + "%' OR CONCAT(p_fname,' ', p_lname) LIKE '%" + query + "%' OR CONCAT(p_lname, ', ', p_fname) LIKE '%"+ query +"%'";
            return dbConnect.view(str);
        }
        public Patient objectFiller(Patient pObject)
        {
            string str = "SELECT * FROM patient_profile WHERE p_id=" + pObject.PatientID.ToString();
            DataTable temp = dbConnect.view(str);
            IEnumerator iter = temp.Rows[0].ItemArray.GetEnumerator();
            iter.MoveNext();
            iter.MoveNext();
            pObject.FName = (String) iter.Current;
            iter.MoveNext();
            pObject.LName = (String)iter.Current;
            iter.MoveNext();
            pObject.MName = (String)iter.Current;
            iter.MoveNext();
            pObject.Birthday = (DateTime) iter.Current;
            iter.MoveNext();
            pObject.Sex = (String) iter.Current;
            iter.MoveNext();
            pObject.Address = (String) iter.Current;
            iter.MoveNext();
            pObject.TelNo = (String)iter.Current;
            iter.MoveNext();
            pObject.CelNo = (String)iter.Current;
            iter.MoveNext();
            pObject.FatherName = (String)iter.Current;
            iter.MoveNext();
            pObject.FatherOccu = (String)iter.Current;
            iter.MoveNext();
            pObject.MotherName = (String)iter.Current;
            iter.MoveNext();
            pObject.MotherOccu = (String)iter.Current;
            iter.MoveNext();
            pObject.SiblingCount = (int)iter.Current;
            iter.MoveNext();
            pObject.AnnualInc = (float) iter.Current;
            iter.MoveNext();
            pObject.Refferal = (String)iter.Current;
            iter.MoveNext();

            return pObject;
        }
        public bool delete(string patientID)
        {
            string str = "DELETE from patient_profile WHERE p_id=" + patientID;
            return dbConnect.delete(str);
        }
    }
}
